import pymysql

#编写创建各表的SQL语句

#1.创建USERS表
USERS = '''
        CREATE TABLE IF NOT EXISTS USERS
        (
            USER_ID VARCHAR(32) NOT NULL,
            PASSWORD CHAR(8) NOT NULL,
            EMAIL VARCHAR(100) NOT NULL,
            AGE TINYINT NOT NULL,
            GENDER ENUM('男','女') NOT NULL DEFAULT '男',
            
            PRIMARY KEY(USER_ID)
        )
        '''

#2.创建FIELD表
FIELD = '''
        CREATE TABLE IF NOT EXISTS FIELD
        (
            FIELD_ID VARCHAR(1) NOT NULL,
            NAME ENUM('艺','衣','食','住','行','学','文') NOT NULL DEFAULT '艺',
            
            PRIMARY KEY(FIELD_ID)
        )
        '''
#3.创建USER_CONTENTS表
USER_CONTENTS = '''
                CREATE TABLE IF NOT EXISTS USER_CONTENTS
                (
                    CONTENT_ID INT NOT NULL AUTO_INCREMENT,
                    TYPE ENUM('动态','项目','文章') NOT NULL,
                    DATE DATETIME  NOT NULL,
                    READVALUE INT NOT NULL,
                    DETAIL JSON NOT NULL,
                    USER_ID VARCHAR(32) NOT NULL,
                    FIELD_ID VARCHAR(1) NOT NULL,
                    
                    PRIMARY KEY(CONTENT_ID),
                    FOREIGN KEY(USER_ID) REFERENCES USERS(USER_ID) ON DELETE CASCADE ON UPDATE CASCADE,
                    FOREIGN KEY(FIELD_ID) REFERENCES FIELD(FIELD_ID) ON DELETE CASCADE ON UPDATE CASCADE
                )
                '''
#4.创建AGREE表
AGREE = '''
        CREATE TABLE IF NOT EXISTS AGREE
        (
            AGREE_ID INT NOT NULL AUTO_INCREMENT,
            TIME DATETIME  NOT NULL,
            CONTENT_ID INT NOT NULL,
            USER_ID VARCHAR(32) NOT NULL,
            
            PRIMARY KEY(AGREE_ID),
            FOREIGN KEY(CONTENT_ID) REFERENCES USER_CONTENTS(CONTENT_ID) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY(USER_ID) REFERENCES USERS(USER_ID) ON DELETE CASCADE ON UPDATE CASCADE
        )
        '''
#5.创建DISCUSS表
DISCUSS = '''
        CREATE TABLE IF NOT EXISTS DISCUSS
        (
            DISCUSS_ID INT NOT NULL AUTO_INCREMENT,
            TIME DATETIME  NOT NULL,
            DETAIL VARCHAR(300) NOT NULL,
            CONTENT_ID INT NOT NULL,
            USER_ID VARCHAR(32) NOT NULL,
            
            PRIMARY KEY(DISCUSS_ID),
            FOREIGN KEY(CONTENT_ID) REFERENCES USER_CONTENTS(CONTENT_ID) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY(USER_ID) REFERENCES USERS(USER_ID) ON DELETE CASCADE ON UPDATE CASCADE
        )
          '''
#6.创建COLLECT表
COLLECT = '''
        CREATE TABLE IF NOT EXISTS COLLECT
        (
            COLLECT_ID INT NOT NULL AUTO_INCREMENT,
            TIME DATETIME  NOT NULL,
            CONTENT_ID INT NOT NULL,
            USER_ID VARCHAR(32) NOT NULL,
            
            PRIMARY KEY(COLLECT_ID),
            FOREIGN KEY(CONTENT_ID) REFERENCES USER_CONTENTS(CONTENT_ID) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY(USER_ID) REFERENCES USERS(USER_ID) ON DELETE CASCADE ON UPDATE CASCADE
        )
          '''

#a.打开数据库连接
database = pymysql.connect(host='localhost'
                           ,user='root'
                           ,password='000000'
                           ,port=3306
                            )
print("已经连接到Mysql！")
#b.使用cursor()方法创建一个游标对象cur
cur = database.cursor()
cur.execute('CREATE DATABASE IF NOT EXISTS WHXD')
cur.close()
print("已经创建WHXD数据库，并关闭连接！")

#a.打开数据库连接
database = pymysql.connect(host='localhost'
                           ,user='root'
                           ,password='000000'
                           ,port=3306
                           ,database='WHXD')
print("已经连接到WHXD数据库！")

#b.使用cursor()方法创建一个游标对象cur
cur = database.cursor()

#c.使用execute()方法执行SQL语句
cur.execute(USERS)
cur.execute(FIELD)
cur.execute(USER_CONTENTS)
cur.execute(AGREE)
cur.execute(DISCUSS)
cur.execute(COLLECT)

sql = '''
     INSERT INTO FIELD (FIELD_ID,NAME) VALUES ('1','艺')
      '''
cur.execute(sql)
database.commit()
sql = '''
     INSERT INTO FIELD (FIELD_ID,NAME) VALUES ('2','衣')
      '''
cur.execute(sql)
database.commit()
sql = '''
     INSERT INTO FIELD (FIELD_ID,NAME) VALUES ('3','食')
      '''
cur.execute(sql)
database.commit()
sql = '''
     INSERT INTO FIELD (FIELD_ID,NAME) VALUES ('4','住')
      '''
cur.execute(sql)
database.commit()
sql = '''
     INSERT INTO FIELD (FIELD_ID,NAME) VALUES ('5','行')
      '''
cur.execute(sql)
database.commit()
sql = '''
     INSERT INTO FIELD (FIELD_ID,NAME) VALUES ('6','学')
      '''
cur.execute(sql)
database.commit()
sql = '''
     INSERT INTO FIELD (FIELD_ID,NAME) VALUES ('7','文')
      '''
cur.execute(sql)
database.commit()


#d.关闭数据库连接
database.close()
print("已经创建各表并插入领域数据，并关闭WHXD数据库连接！")
